iT邦幫忙

2019 iT 邦幫忙鐵人賽

DAY 8
0
自我挑戰組

Access VBA 之 iT管理實做系列 第 8

Access VBA 之 iT管理實做Day8: 電腦硬體表-從User端產生

  • 分享至 

  • xImage
  •  

電腦硬體表,是用來紀錄PC主機上的詳細資料,跟資產表不同的是,它只針對電腦記載了相關紀錄,它隸屬於資產表的分支。
電腦硬體紀錄,如果公司使用的是Windows作業系統,可以很簡單的使用VBS腳本搭配WMI語法來取得相關的硬體資訊,網路上可以找到許多相關資訊,原本筆者是透過我的工作電腦,連線到各台電腦取得這些資訊,但後來發現防火牆會擋掉這個連線需求,要再額外設定防火牆似乎不太合乎時間成本,於是就透過公司內的AD來執行,如果您的公司沒有AD架構,則可以手動於該台電腦執行,除非更換硬體,否則應該執行一次記錄下來即可,執行後,此VBS腳本會把該台的硬體、軟體資訊,紀錄到檔案伺服器特定位置的文字檔內,以便我們之後取用。未來有更新硬體時,再執行一次,即可再轉出資料,之後透過軟體對照,再更新差異處即可。

以下是筆者目前使用的VBS程式內容:


DigPC-Localhost.vbs


'用於 AD 的指令檔,取出user端電腦的資訊,並轉到 strOutputFilePath 路徑之中,請自行修改路徑

Set objFSO = CreateObject("Scripting.FileSystemObject")
strOutputFilePath = "\\FileServer\ShareFolder\"


      strComputer = "localhost"
      If Ping(strComputer) = True Then
            On Error Resume Next
            Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
            If Err.Number = 0 Then
                  On Error GoTo 0
                  
                  ' GET NETWORK SETTING
                  Set colAdapters = objWMIService.ExecQuery _
                  ("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
                   MAC_address = ""
                   IP_address = ""
                   Subnet = ""
                   Default_gateway = ""
                   DNS_Server = ""
 
					For Each objAdapter in colAdapters
					    Network_Description = objAdapter.Description
					   MAC_address = objAdapter.MACAddress
					   If Not IsNull(objAdapter.IPAddress) Then
					      For i = 0 To UBound(objAdapter.IPAddress)
					         IP_address = objAdapter.IPAddress(i)
					      Next
					   End If
					 
					   If Not IsNull(objAdapter.IPSubnet) Then
					      For i = 0 To UBound(objAdapter.IPSubnet)
					         Subnet = objAdapter.IPSubnet(i)
					      Next
					   End If
					 
					   If Not IsNull(objAdapter.DefaultIPGateway) Then
					      For i = 0 To UBound(objAdapter.DefaultIPGateway)
					         Default_gateway = objAdapter.DefaultIPGateway(i)
					      Next
					   End If
					   If Not IsNull(objAdapter.DNSServerSearchOrder) Then
					      For i = 0 To UBound(objAdapter.DNSServerSearchOrder)
					         DNS_Server = objAdapter.DNSServerSearchOrder(i)
					      Next
					   End If

					Next

'電腦Family
   strPC_Family=""
   Set colItems = objWMIService.ExecQuery("SELECT Version FROM Win32_ComputerSystemProduct")
   For Each objItem In colItems
      strPC_Family = objItem.Version
   Next


                  ' GET THE SERIAL NUMBER
                  strMARK = ""
                  strSN = ""
                  Set colBIOS = objWMIService.ExecQuery ("Select Manufacturer, SerialNumber from Win32_BIOS")
                  For Each objBIOS In colBIOS
                        strMARK   = objBIOS.Manufacturer
                        strSN   = objBIOS.SerialNumber
                  Next
                  
                  ' GET THE COMPUTER MODEL AND HOSTNAME
                        strModel = ""
                        strHostname = ""
                        strTimeZone = ""
                        strDaylight = ""
                        strUserName = ""
                  Set colComputer = objWMIService.ExecQuery _
                      ("Select Model, Name, CurrentTimeZone, UserName,  DaylightInEffect from Win32_ComputerSystem")
                  
                  For Each objComp In colComputer
                        strModel = objComp.Model
                        strHostname = objComp.Name
                        strTimeZone = objComp.CurrentTimeZone
                        strDaylight = objComp.DaylightInEffect
                        strUserName = objComp.UserName
                  Next
                  
                  
'實體記憶體容量
strMEM2=""
   Set colItems = objWMIService.ExecQuery("SELECT Capacity FROM Win32_PhysicalMemory")
   For Each objItem In colItems
      strMEM2 = strMEM2  & "(" & Int(objItem.Capacity /(1048576)) & "MB) "
   Next

                  
                  'GET CPU INFO
                  strCPU=""
                  Set colSettings = objWMIService.ExecQuery ("Select Name from Win32_Processor")
                  For Each objProcessor in colSettings 
                      strCPU = objProcessor.Name
                  Next
                  
					'GET OS INFO
					strFreeMEM=""
					OS_Name=""
					OS_BootDevice=""
					OS_WindowsDirectory=""
					OS_Language=""
					strPCDescription=""
					Set colSettings = objWMIService.ExecQuery ("Select FreePhysicalMemory, Caption, BootDevice, WindowsDirectory, Description, OSLanguage from Win32_OperatingSystem")
					For Each objOperatingSystem in colSettings
						 strFreeMEM = "(" & Int(objOperatingSystem.FreePhysicalMemory / (1024)) & "MB)"
					     OS_Name = objOperatingSystem.Caption
					     OS_BootDevice = objOperatingSystem.BootDevice
					     OS_WindowsDirectory = objOperatingSystem.WindowsDirectory
					     strPCDescription  = objOperatingSystem.Description
					     OS_Language  = objOperatingSystem.OSLanguage
							Select Case OS_Language
							Case "1028"
							   OS_Language = "CT"
							Case "1033"
							   OS_Language = "EN"
							Case "3082"
							   OS_Language = "ES"
							Case Else
							   
							End Select 
					Next
					
					'GET SYSTEM SLOT Info
					
					Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
					Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_SystemSlot", "WQL", wbemFlagReturnImmediately + wbemFlagForwardOnly)
					strSystemSlot =  ""
					For Each objItem In colItems
						strSystemSlotNumbers ="(" &  objItem.CurrentUsage  & ") " 
						strSystemSlot  = strSystemSlot & "/"  &  objItem.SlotDesignation  
					Next
					strSystemSlot = strSystemSlotNumbers  &  strSystemSlot  

					'GET SOUND DEVICE
					Sound_Manufacturer=""
					Set colItems = objWMIService.ExecQuery("Select Description from Win32_SoundDevice")
					For Each objItem in colItems
					    Sound_Manufacturer = objItem.Description
					Next

					'GET DISPLAY CONFIGURATION
					Display_SettingID=""
					Set colItems = objWMIService.ExecQuery("Select SettingID from Win32_DisplayConfiguration")
					For Each objItem in colItems
					    Display_SettingID = objItem.SettingID
					Next
					
					
					'GET DISK INFO
					srtHDD1=""
					srtHDD2=""
					Set colItems = objWMIService.ExecQuery("Select Size, Model from Win32_DiskDrive")
					For Each objItem in colItems
						srtHDD1 = srtHDD1 & "(" & Int(objItem.Size /(1000000000)) & "GB) "
						srtHDD2 = srtHDD2 & objItem.Model & "(" & Int(objItem.Size /(1000000000)) & "GB) "
					Next

					strHDD3=""
					Set colItems = objWMIService.ExecQuery("Select DriveType, FreeSpace, Size from Win32_LogicalDisk")
					' Loop to enumerate LogicalDisk properties
					For Each objItem in colItems
					
					Select Case objItem.DriveType
						Case "2"
							strHDD3 = strHDD3 & objItem.DeviceID & "(FDD) "
						Case "5"
							strHDD3 = strHDD3 & objItem.DeviceID & "(CDROM) "
						Case "4"
							strHDD3 = strHDD3 & objItem.DeviceID & "(MAP) "
						Case Else
							strHDD3 = strHDD3 & objItem.DeviceID &"("& Int(objItem.FreeSpace /(1000000000)) &"/"& Int(objItem.Size /(1000000000)) & "GB) "
					End Select 

					Next

' Export Data
				strResultsFile =  strOutputFilePath & "DigPC-Localhost.csv"
				strNoContactFile =  strOutputFilePath &  "DigPC-Localhost_ERROR.csv"

				If objFSO.FileExists(strResultsFile) = FALSE then
					Set objOutputFile = objFSO.CreateTextFile(strResultsFile, True)
                  objOutputFile.Write "DateTime,Host Name,UserName,IP Address,Subnet,Gateware,DNS Server,Desc,MAC Address,Mark,Family,Model,Serial Number,CPU,RAM,HDD1,HDD2,DRIVE,Sound Card,Video Card,Slot,OS,LN,BootPartation,BootDIR,TimeZone,Daylight"
				Else
					Set objOutputFile = objFSO.OpenTextFile(strResultsFile, 8,2)
				End if

				If objFSO.FileExists(strNoContactFile) = FALSE then
					Set objNoContactFile = objFSO.CreateTextFile(strNoContactFile, True)
				Else
					Set objNoContactFile = objFSO.OpenTextFile(strNoContactFile, 8,2)
				End if
                  '匯出資料:設定資料'硬體資訊'系統狀態'時區與日光節約
                  ' Replace(TimeNow(Now), "-", "")
                  objOutputFile.Write VbCrLf & _
                  """" & Replace(TimeNow(Now), "-", "") & """,""" & Trim(strHostname) & """,""" & Trim(strUserName) & """,""" & Trim(IP_address) & """,""" & Trim(Subnet) & """,""" & Trim(Default_gateway) & """,""" & Trim(DNS_Server) & """,""" & Trim(strPCDescription) & """,""" & Trim(MAC_address) & """,""" & Trim(strMARK) & """,""" & Trim(strPC_Family) & """,""" & Trim(strModel) & """,""" & Trim(strSN) & """,""" & Trim(strCPU) & """,""" & Trim(strFreeMEM &" / " & strMEM2) & """,""" & Trim(srtHDD1) & """,""" & Trim(srtHDD2) & """,""" & Trim(strHDD3)  & """,""" & Trim(Sound_Manufacturer) & """,""" & Trim(Display_SettingID) & """,""" & Trim(strSystemSlot)  & """,""" & Trim(OS_Name)& """,""" & Trim(OS_Language) & """,""" & Trim(OS_BootDevice) & """,""" & Trim(OS_WindowsDirectory)  & """,""" & Trim(strTimeZone) & """,""" & Trim(strDaylight) & """"
            Else
                  Err.Clear
                  On Error GoTo 0
                  objNoContactFile.WriteLine Trim(strComputer)
            End If
      Else
            objNoContactFile.WriteLine Trim(strComputer)
      End If


objOutputFile.Close
objNoContactFile.Close

Set objOutputFile = Nothing
Set objNoContactFile = Nothing

' MsgBox "Finished processing the input file." & VbCrLf & "Results are in: " & strResultsFile & VbCrLf & "PCs not found: " & strNoContactFile

Function Ping(strComputer)
      Dim objShell, boolCode
      Set objShell = CreateObject("WScript.Shell")
      boolCode = objShell.Run("Ping -n 1 -w 300 " & strComputer, 0, True)
      If boolCode = 0 Then
            Ping = True
      Else
            Ping = False
      End If
End Function

Function TimeNow(dDateTime)
      TimeNow =      Year(Now) & "-" &_
                        Right("00" & Month(Now), 2) & "-" &_
                        Right("00" & Day(Now), 2) & "-" &_
                        Right("00" & Hour(Now), 2) & "-" &_
                        Right("00" & Minute(Now), 2) & "-" &_
                        Right("00" & Second(Now), 2)
End Function

WSCript.Quit

此程式產生的硬體資料儲存到DigPC-Localhost.csv這個檔案中,後面程式取用後,會改變檔名,以便讓DigPC-Localhost.csv這個檔案內的,都是尚未取用的項目。


上一篇
Access VBA 之 iT管理實做Day7: 資產資料
下一篇
Access VBA 之 iT管理實做Day9: 電腦硬體表取得-匯入管理端
系列文
Access VBA 之 iT管理實做30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言